Well, I'm still working on migrating our databases to 6.4.2.
Since pg_dump in 6.2.1 doesn't dump an acceptable schema for 6.4, I decided
to use the schema-creation scripts we have, and automate the task. This is
helpful because in our site we create many databases with identical schema
and different data for different users.
Thus, I perform the following, for each pair of database and owner name:
-- connect to the new server as the postgres superuser and drop the
database "X" if it exists
-- connect as the owner and create the database "X"
-- connect to "X" as its owner and run the appropriate schema-creation SQL
script
-- run pg_dump -a from the old server, and pipe the output to the new
server, database "X".
Now, I don't create any sequences in my schema-cretion SQL scripts, because
the up-to-date sequences are dumped by pg_dump -a, and creating them in the
schema stage will cause them to be declared "existing", and thus have their
initial values rather than updated values when the dump is done.
Now here is the problem:
Running the following command as user 'royya' (the owner of this specific
database):
performance_test=> \i performance.sql
-- Table performance
DROP TABLE performance;
ERROR: pg_ownercheck: class "performance" not found
CREATE TABLE performance (
id int4 DEFAULT nextval('sq_per') NOT NULL,
eng_text text NOT NULL,
heb_text text NOT NULL
);
ERROR: pg_aclcheck: class "sq_per" not found
...
The first error is expected. The script drops the table, and if it doesn't
exist, there's an error. The second error is NOT expected.
"Why not?" you may ask, "If the sequence doesn't exist, why should it allow
basing a default value on it?", well, very simple - it allows me to do it
if I do it from *my* user. And I really see no reason why it should check
for it at creation time. After all, the name of the sequence is a mere
string parameter to a function call. If the function exists and accepts a
string, that's all it should care about, or am I wrong?
In any case, if it works from my user, why shouldn't it work from his?
There is, of course, a difference between our users - mine was created as
"allowed to create users", his is only allowed to create databases.
Bug? Feature? Suggestions?
Herouth
--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma